package com.bosch.in.rtp1.ept.dao.impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import com.bosch.in.rtp1.ept.constant.HCFilterColumnName;
import com.bosch.in.rtp1.ept.constant.HoldCodeType;
import com.bosch.in.rtp1.ept.dao.HoldCodeFilterDao;
import com.bosch.in.rtp1.ept.model.dto.HoldCodeFilter;
import com.bosch.in.rtp1.ept.util.SQLStatementLoader;
import com.bosch.in.rtp1.ept.util.StringUtility;

/**
 * Concrete JDBC implementation of LotStatusDao. Provide basic CRUD action and various searching on LotStatus table.
 * @author TPR1HC
 *
 */
public class HoldCodeFilterJDBCDao extends GenericDao implements HoldCodeFilterDao {
	
	private static final Logger LOGGER = Logger.getLogger(HoldCodeFilterJDBCDao.class);
	
	/**
	 * Constructor to inject connection
	 * @param con	DB connection
	 */
	public HoldCodeFilterJDBCDao(Connection con) {
		super(con);
	}
	
	@Override
	public List<HoldCodeFilter> getHoldCodeFilters(HoldCodeType hcType) throws SQLException{
		LOGGER.debug("Begin HoldCodeFilterJDBCDao.getHoldCodeFilters");
		
		List<HoldCodeFilter> hcFilters = new ArrayList<HoldCodeFilter>();
		
		// Execute query to load HoldCodeFilter values
		ResultSet resultSet = executeQuery(SQLStatementLoader.getHCFilterQuery(), new Object[] {hcType.toInt()});   
		while (resultSet.next()) {
			HoldCodeFilter hcFilter = new HoldCodeFilter();
			hcFilter.setId(resultSet.getInt(HCFilterColumnName.ID_DB));
			hcFilter.setComment(resultSet.getString(HCFilterColumnName.COMMENT_DB));
			hcFilter.setPatterns(StringUtility.extractHCPatterns(resultSet.getString(HCFilterColumnName.PATTERNS_DB)));
			hcFilter.setOrder(resultSet.getInt(HCFilterColumnName.ORDER_DB));
			
			hcFilters.add(hcFilter);
		} 
		// close statement, result set
		resultSet.close();
		close();
		LOGGER.debug("End HoldCodeFilterJDBCDao.getHoldCodeFilters");
		return hcFilters;
	}

	@Override
	public boolean updateHoldCodeFilter(List<HoldCodeFilter> hcFilters)
			throws SQLException {
		LOGGER.debug("Begin HoldCodeFilterJDBCDao.updateHoldCodeFilter");
		HoldCodeFilter hcFilter = hcFilters.get(0);
		// set parameter values for updated HoldCode
		Object[] params = new Object[4];
		params[0] = hcFilter.getComment();
		params[1] = StringUtility.convertHCPatternsToStr(hcFilter.getPatterns());
		params[2] = hcFilter.getOrder();
		params[3] = hcFilter.getId();
		LOGGER.debug("End HoldCodeFilterJDBCDao.updateHoldCodeFilter");
		return executeUpdate(SQLStatementLoader.getHCFilterUpdate(), params);
	}

	@Override
	public int insertHoldCodeFilter(HoldCodeFilter hcFilter) throws SQLException {
		LOGGER.debug("Begin HoldCodeFilterJDBCDao.insertHoldCodeFilter");
		Object[] params = new Object[4];
		// set parameter values for new HoldCode
		params[0] = hcFilter.getComment();
		params[1] = StringUtility.convertHCPatternsToStr(hcFilter.getPatterns());
		params[2] = hcFilter.getOrder();
		params[3] = hcFilter.getType().toInt();
		LOGGER.debug("End HoldCodeFilterJDBCDao.insertHoldCodeFilter");
		return executeInsert(SQLStatementLoader.getHCFilterInsert(), params);
	}

	@Override
	public boolean deleteHoldCodeFilter(int id) throws SQLException {
		LOGGER.debug("Begin HoldCodeFilterJDBCDao.deleteHoldCodeFilter");
		// set parameter values for deleted HoldCode identifier
		Object[] params = new Object[1];
		params[0] = id;
		LOGGER.debug("End HoldCodeFilterJDBCDao.deleteHoldCodeFilter");
		return executeDelete(SQLStatementLoader.getHCFilterDelete(), params);
	}

	@Override
	public int getLastOrderOfHoldCodeFilter(HoldCodeType hcType) throws SQLException {
		LOGGER.debug("Begin HoldCodeFilterJDBCDao.getLastOrderOfHoldCodeFilter");
		int order = 0;
		// Execute query to get last order of holdcode filter
		ResultSet resultSet = executeQuery(SQLStatementLoader.getLastOrderOfHCFilter(), new Object[] {hcType.toInt()});           
		while (resultSet.next()) {
			order = resultSet.getInt(1);
		}
		// close statement, result set
		resultSet.close();
		close();
		LOGGER.debug("End HoldCodeFilterJDBCDao.getLastOrderOfHoldCodeFilter");
		return order;
	}

	@Override
	public boolean reorderHoldCodeFilters(List<Integer> idList)
			throws SQLException {
		LOGGER.debug("Begin HoldCodeFilterJDBCDao.reorderHoldCodeFilters");
		Object[][] params = new Object[idList.size()][];
		// reorder for all HoldCodes
		for (int i = 0; i < idList.size(); i++) {
			params[i] = new Object[2];
			params[i][0] = i + 1;
			params[i][1] = idList.get(i);
		}
		LOGGER.debug("End HoldCodeFilterJDBCDao.reorderHoldCodeFilters");
		return executeBatchUpdate(SQLStatementLoader.getHCFilterReOder(), params);
	}

	@Override
	public List<HoldCodeFilter> getAllHoldCodeFilters() throws SQLException {
		LOGGER.debug("Begin HoldCodeFilterJDBCDao.getAllHoldCodeFilters");
		
		List<HoldCodeFilter> hcFilters = new ArrayList<HoldCodeFilter>();
		
		// Execute query to load HoldCodeFilter values
		ResultSet resultSet = executeQuery(SQLStatementLoader.getHCAllFilterQuery(), null);   
		while (resultSet.next()) {
			HoldCodeFilter hcFilter = new HoldCodeFilter();
			hcFilter.setId(resultSet.getInt(HCFilterColumnName.ID_DB));
			hcFilter.setComment(resultSet.getString(HCFilterColumnName.COMMENT_DB));
			
			hcFilter.setPatterns(StringUtility.extractHCPatterns(resultSet.getString(HCFilterColumnName.PATTERNS_DB)));
			hcFilter.setOrder(resultSet.getInt(HCFilterColumnName.ORDER_DB));
			int type = resultSet.getInt(HCFilterColumnName.TYPE_DB);
			if (type == HoldCodeType.HC.toInt()) {
				hcFilter.setType(HoldCodeType.HC);
			} else if (type == HoldCodeType.HC_RECLASF.toInt()) {
				hcFilter.setType(HoldCodeType.HC_RECLASF);
			} else {
				hcFilter.setType(HoldCodeType.HC_ACTIVE);
			}
			hcFilter.setOrder(resultSet.getInt(HCFilterColumnName.ORDER_DB));
			hcFilters.add(hcFilter);
		}
		resultSet.close();
		// close statement, result set
		close();
		LOGGER.debug("End HoldCodeFilterJDBCDao.getAllHoldCodeFilters");
		return hcFilters;
	}

	@Override
	public boolean setLastPosition(HoldCodeFilter hcFilter) throws SQLException {
		LOGGER.debug("Begin HoldCodeFilterJDBCDao.setLastPosition");
		
		// set parameter values for setting last position
		Object[] params = new Object[3];
		params[0] = hcFilter.getOrder();
		params[1] = hcFilter.getType().toInt();
		params[2] = hcFilter.getComment();
		LOGGER.debug("End HoldCodeFilterJDBCDao.setLastPosition");
		return executeUpdate(SQLStatementLoader.getHCFilterSetLastPos(), params);
	}
}
